﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Org.BouncyCastle.Crypto.Engines;

namespace CreditCard
{
    internal class MySqlDao
    {
        static string url = @"server=localhost;Allow User Variables=True;database=credit_card;uid=root;pwd=123456;charset=utf8;";

        public MySqlConnection getConnection()
        {
            MySqlConnection conn;
            try
            {
                conn = new MySqlConnection(url);
                conn.Open();
                return conn;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        MySqlDataAdapter adapter;

        MySqlDataReader reader;

        DataSet ds;

        MySqlConnection conn;

        MySqlCommand comm;

        string sql;

        public User userLogin(string id, string pass)
        {
            User user = new User();
            user.U_id = "";
            try
            {
                conn = getConnection();
                sql = "select u_id, u_pass, u_name, u_tel, u_type from users where u_id=@id and u_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    user.U_id = reader["u_id"].ToString();
                    user.U_tel = reader["u_tel"].ToString();
                    user.U_type = Convert.ToInt32(reader["u_type"].ToString());
                    user.U_name = reader["u_name"].ToString();
                    user.U_pass = reader["u_pass"].ToString();
                }
                return user;
            }
            catch (Exception ex)
            {
                return user;
            }
            finally
            {
                conn.Close();
            }
        }

        public Admin adminLogin(string id, string pass)
        {
            Admin admin = new Admin();
            admin.A_id = "";
            try
            {
                conn = getConnection();
                sql = "select a_id, a_pass from admin where a_id=@id and a_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    admin.A_id = reader["a_id"].ToString();
                    admin.A_pass = reader["a_pass"].ToString();
                }
                return admin;
            }
            catch (Exception ex)
            {
                return admin;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<BaseCard> getBase()
        {
            List<BaseCard> list = new List<BaseCard>();
            try
            {
                conn = getConnection();
                sql = "select b_id,b_type,b_limit,b_term from base_card";
                comm = new MySqlCommand(sql, conn);
                reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    BaseCard card = new BaseCard();
                    card.B_id = reader["b_id"].ToString();
                    card.B_type = reader["b_type"].ToString();
                    card.B_limit = Convert.ToDecimal(reader["b_limit"].ToString());
                    card.B_term = Convert.ToInt32(reader["b_term"].ToString());
                    list.Add(card);
                }
                return list;
            }
            catch (Exception ex)
            {
                return list;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool insertCard(Card card)
        {
            try
            {
                conn = getConnection();
                sql = "insert into card(c_id,b_id,u_id,c_pass,c_time)" +
                    "values(@c_id,@b_id,@u_id,@c_pass,@c_time)";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("c_id", card.C_id);
                comm.Parameters.AddWithValue("b_id", card.B_id);
                comm.Parameters.AddWithValue("u_id", card.U_id);
                comm.Parameters.AddWithValue("c_pass", card.C_pass);
                comm.Parameters.AddWithValue("c_time", card.C_time);
                return comm.ExecuteNonQuery()>0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
        public DataSet selectCard(string u_id, int state)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT c_id 卡号, b_type 类型, b_limit 额度, b_term 期限, c_time 有效期至 " +
                    "FROM card JOIN base_card USING(b_id) where u_id=@u_id and c_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("u_id", u_id);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectCard(int state)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT u_id 申请人,u_name 姓名, u_tel 电话, c_id 卡号, b_type 类型, b_limit 额度, b_term 期限, c_time 有效期至 " +
                    "FROM card JOIN base_card USING(b_id) join users using(u_id) where c_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }
        public bool updateCardState(string c_id, int c_state)
        {
            try
            {
                conn = getConnection();
                sql = "update card set c_state=@state where c_id=@id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", c_id);
                comm.Parameters.AddWithValue("state", c_state);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectTransfer(string u_id)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT t_id 账单号, card.c_id 卡号, t_amount 金额, t_time 时间 " +
                    "FROM card JOIN users USING(u_id) JOIN transfer USING(c_Id) " +
                    " WHERE u_id=@u_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("u_id", u_id);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }
        public bool updateUser(User user)
        {
            try
            {
                conn = getConnection();
                sql = "update users set u_name=@name,u_tel=@tel where u_id=@id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", user.U_id);
                comm.Parameters.AddWithValue("tel", user.U_tel);
                comm.Parameters.AddWithValue("name", user.U_name);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool insertMsg(Msg msg)
        {
            try
            {
                conn = getConnection();
                sql = "insert into message(m_id,m_msg,u_id,m_time)" +
                    "values(@m_id,@m_msg,@u_id,@m_time)";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("m_id", msg.M_id);
                comm.Parameters.AddWithValue("u_id", msg.U_id);
                comm.Parameters.AddWithValue("m_msg", msg.M_msg);
                comm.Parameters.AddWithValue("m_time", msg.M_time);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateMsgState(string m_id, int state)
        {
            try
            {
                conn = getConnection();
                sql = "update message set m_state=@state where m_id=@m_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("m_id", m_id);
                comm.Parameters.AddWithValue("state", state);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectMsg(string u_id, int state)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT m_id 信息ID, m_msg 内容, m_time 时间 from message where u_id=@u_id and m_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", state);
                comm.Parameters.AddWithValue("u_id", u_id);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

    }
}
